package com.egao.orderB.biz.impl;

import com.egao.common.core.damon.results.BaseDao;
import com.egao.common.core.damon.results.PageResult2;
import com.egao.common.core.damon.utils.Utils;
import com.egao.common.core.web.JsonResult;
import com.egao.common.system.entity.User;
import com.egao.orderB.biz.ApplyCustomsFeeBiz;
import com.egao.orderB.entity.ApplyCustoms;
import com.egao.orderB.entity.ApplyCustomsFee;
import org.apache.poi.ss.formula.functions.T;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.SingleColumnRowMapper;
import org.springframework.stereotype.Repository;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 *
 * 数据层实现类
 * @author : Doman
 * @date : 2021-09-08 14:39:54
 * @version ：1.0
 */
@Repository
public class ApplyCustomsFeeBizImpl extends BaseDao implements ApplyCustomsFeeBiz {

	@Override
	public JsonResult list(PageResult2<T> page, ApplyCustomsFee applyCustomsFee, User loginUser) throws Exception {
		Map<String, String> map  = new HashMap<String, String>();
		RowMapper<ApplyCustomsFee> rm = new BeanPropertyRowMapper<ApplyCustomsFee>(ApplyCustomsFee.class);
		StringBuffer sql = new StringBuffer("select a.id,a.organization_id,a.apply_id");
    sql.append(",a.type,a.fee_name,a.price,a.standard");
		sql.append(",a.fee_state,a.fee_person,a.fee_time,a.fobject");
		sql.append(",right(a.fee_modules,char_length(a.fee_modules)-1) fee_modules");
		sql.append(",a.invoiceid,a.invoicename,a.driver_name");
		sql.append(",a.invoicenum,a.invoicestate,a.currency,a.exchange_rate");
		sql.append(",a.settlement,a.settlement_price,a.remark,a.create_time");
		sql.append(",a.create_user,a.update_time,a.update_user");
    sql.append(",sogzn.organization_name organizationName,suser.username createName ");
    sql.append(" from chf_apply_customs_fee a ");
    sql.append(" inner join sys_user suser on suser.user_id=a.create_user ");
    sql.append(" inner join sys_organization sogzn on sogzn.organization_id=a.organization_id and sogzn.deleted=0 ");
    sql.append(" where a.deleted=0 and a.apply_id=:apply_id ");
		map.put("apply_id", applyCustomsFee.getApplyId());
    if(Utils.isNotEmpty(applyCustomsFee)){
			if (Utils.isNotEmpty(applyCustomsFee.getType())) {
				sql.append(" and instr(a.type,:type)>0 ");
				map.put("type", applyCustomsFee.getType());
			}
			if (Utils.isNotEmpty(applyCustomsFee.getFeeName())) {
				sql.append(" and instr(a.fee_name,:fee_name)>0 ");
				map.put("fee_name", applyCustomsFee.getFeeName());
			}
			if (Utils.isNotEmpty(applyCustomsFee.getFeeState())) {
				sql.append(" and instr(a.fee_state,:fee_state)>0 ");
				map.put("fee_state", applyCustomsFee.getFeeState());
			}
			if (Utils.isNotEmpty(applyCustomsFee.getFobject())) {
				sql.append(" and instr(a.fobject,:fobject)>0 ");
				map.put("fobject", applyCustomsFee.getFobject());
			}
      if(Utils.isNotEmpty(applyCustomsFee.getSort())&&Utils.isNotEmpty(applyCustomsFee.getOrder())){
        sql.append(" order by a."+applyCustomsFee.getSort()+" "+applyCustomsFee.getOrder());
      } else {
        sql.append(" order by a.create_time desc");
      }
		}
		return jsonResult(getPageQueryList(page, sql.toString(), map, rm));
	}

	@Override
	public List<ApplyCustomsFee> list(ApplyCustomsFee applyCustomsFee, User loginUser) {
		Map<String, String> map  = new HashMap<String, String>();
		RowMapper<ApplyCustomsFee> rm = new BeanPropertyRowMapper<ApplyCustomsFee>(ApplyCustomsFee.class);
		StringBuffer sql = new StringBuffer();
		sql.append("select  ");
		sql.append("cc.id,cc.organization_id,cc.apply_id,cc.type");
		sql.append(",cc.fee_name");
		sql.append(",cc.standard,cc.price,cc.fee_state,cc.fee_person,cc.fee_time,cc.remark,cc.fobject,cc.fee_modules");
		sql.append(",cc.invoiceid,cc.invoicenum,cc.invoicename,cc.driverName,cc.invoicestate,cc.currency,cc.exchange_rate ");
		sql.append(",cc.create_time,cc.create_user,cc.update_time,cc.update_user");
		sql.append(",cc.organizationName,cc.createName ");
		sql.append(",cc.updateName ");
		sql.append(" from(");
		sql.append("select b.id,b.organization_id,b.apply_id,b.type");
		sql.append(",b.fee_name");
		sql.append(",b.standard,b.price,b.fee_state,b.fee_person,b.fee_time,b.remark,b.fobject,right(b.fee_modules,char_length(b.fee_modules)-1) fee_modules,b.fee_modules fee_modules2");
		sql.append(",b.invoiceid,b.invoicenum,b.invoicename,CASE b.driver_name WHEN null THEN d.name WHEN '' THEN d.name  ELSE b.driver_name END driverName,b.invoicestate,b.currency,b.exchange_rate ");
		sql.append(",b.create_time,b.create_user,b.update_time,b.update_user");
		sql.append(",sognz.organization_name organizationName,suser.nick_name createName ");
		sql.append(",suser2.nick_name updateName ");
		sql.append(" from chf_apply_customs_fee b ");
		sql.append(" inner join sys_user suser on suser.user_id=b.create_user ");
		sql.append(" left join sys_user suser2 on suser2.user_id=b.update_user ");
		sql.append(" left join chf_driver d on d.car_no=b.invoicename and d.deleted=0 ");
		sql.append(" inner join sys_organization sognz on sognz.organization_id=b.organization_id and sognz.deleted=0 ");
		sql.append(" where b.deleted=0 and b.apply_id=:apply_id ");
		map.put("apply_id", applyCustomsFee.getApplyId());
    if(Utils.isNotEmpty(applyCustomsFee)){
			if (Utils.isNotEmpty(applyCustomsFee.getType())) {
				sql.append(" and instr(b.type,:type)>0 ");
				map.put("type", applyCustomsFee.getType());
			}
			if (Utils.isNotEmpty(applyCustomsFee.getFeeName())) {
				sql.append(" and instr(b.fee_name,:fee_name)>0 ");
				map.put("fee_name", applyCustomsFee.getFeeName());
			}
			if (Utils.isNotEmpty(applyCustomsFee.getInvoicename())) {
				sql.append(" and instr(b.invoicename,:invoicename)>0 ");
				map.put("invoicename", applyCustomsFee.getInvoicename());
			}
			if (Utils.isNotEmpty(applyCustomsFee.getFeeState())) {
				sql.append(" and instr(b.fee_state,:fee_state)>0 ");
				map.put("fee_state", applyCustomsFee.getFeeState());
			}
			if (Utils.isNotEmpty(applyCustomsFee.getFobject())) {
				sql.append(" and instr(b.fobject,:fobject)>0 ");
				map.put("fobject", applyCustomsFee.getFobject());
			}
//      if(Utils.isNotEmpty(applyCustomsFee.getSort())&&Utils.isNotEmpty(applyCustomsFee.getOrder())){
//        sql.append(" order by a."+applyCustomsFee.getSort()+" "+applyCustomsFee.getOrder());
//      } else {
//        sql.append(" order by a.create_time desc");
//      }
    }
		sql.append(" order by fee_modules2 asc, CONVERT(b.fee_name using gbk) desc");
		sql.append(" ) cc ");
		getMapParameter(sql, map);
		return jdbcTemplate.query(sql.toString(),map, rm);
	}

	@Override
	public List<String> feeNames(ApplyCustoms applyCustoms, List<String> feeNames, User loginUser) {
		Map<String, Object> map = new HashMap<String, Object>();
		RowMapper<String> rm = new SingleColumnRowMapper<String>(String.class);
		StringBuffer sql = new StringBuffer("select distinct ");
		sql.append(" cf.fee_name");
		sql.append(" from chf_apply_customs_fee cf ");
		sql.append(" inner join chf_apply_customs ac on ac.id=cf.apply_id and ac.deleted=0 ");
//		sql.append(" and instr(',发起费用清单,应付费用已审核,应收费用已审核,财务应收应付费用已审核,结单,',b.state)>0 ");
		sql.append(" where cf.deleted=0 ");
		if (Utils.isNotEmpty(applyCustoms)) {
			if (Utils.isNotEmpty(feeNames)) {
				sql.append(" and cf.fee_name not in (:fee_name) ");
				map.put("fee_name", feeNames);
			}
			if (Utils.isNotEmpty(applyCustoms.getDate1())) {
				sql.append(" and ac.create_time>= DATE_FORMAT(:date1,'%Y-%m-%d %H:%i:%s') ");
				map.put("date1", applyCustoms.getDate1());
			}
			if (Utils.isNotEmpty(applyCustoms.getDate2())) {
				sql.append(" and ac.create_time<=DATE_FORMAT(:date2,'%Y-%m-%d %H:%i:%s') ");
				map.put("date2", applyCustoms.getDate2());
			}
			if (Utils.isNotEmpty(applyCustoms.getCustomer())) {
				sql.append(" and cf.invoicename=ac.customer ");
				sql.append(" and instr(ac.customer,:customer)>0 ");
				map.put("customer", applyCustoms.getCustomer());
			}
			if (Utils.isNotEmpty(applyCustoms.getInvoicename())) {
				sql.append(" and instr(cf.invoicename,:invoicename)>0 ");
				map.put("invoicename", applyCustoms.getInvoicename());
			}
			if (Utils.isNotEmpty(applyCustoms.getType())) {
				sql.append(" and instr(cf.type,:type)>0 ");
				map.put("type", applyCustoms.getType());
			}
		}
		getMapParameter(sql, null);
		return jdbcTemplate.query(sql.toString(), map, rm);
	}

	@Override
	public ApplyCustomsFee getById(ApplyCustomsFee applyCustomsFee) {
		RowMapper<ApplyCustomsFee> rm = new BeanPropertyRowMapper<ApplyCustomsFee>(ApplyCustomsFee.class);
		Map<String, String> map  = new HashMap<String, String>();
		StringBuffer sql = new StringBuffer("select a.id,a.organization_id,a.apply_id");
    sql.append(",a.type,a.fee_name,a.price,a.standard");
		sql.append(",a.fee_state,a.fee_person,a.fee_time,a.fobject");
		sql.append(",right(a.fee_modules,char_length(a.fee_modules)-1) fee_modules");
		sql.append(",a.invoiceid,a.invoicename,a.driver_name");
		sql.append(",a.invoicenum,a.invoicestate,a.currency,a.exchange_rate");
		sql.append(",a.settlement,a.settlement_price,a.remark,a.create_time");
		sql.append(",a.create_user,a.update_time,a.update_user");
    sql.append(",sogzn.organization_name organizationName,suser.username createName ");
    sql.append(" from chf_apply_customs_fee a ");
    sql.append(" inner join sys_user suser on suser.user_id=a.create_user ");
    sql.append(" inner join sys_organization sogzn on sogzn.organization_id=a.organization_id and sogzn.deleted=0 ");
    sql.append(" where a.deleted=0 and a.id=:id ");
		map.put("id", applyCustomsFee.getId());
		getMapParameter(sql, map);
		return jdbcTemplate.queryForObject(sql.toString(), map, rm);
	}
	@Override
	public boolean save(ApplyCustomsFee applyCustomsFee, User loginUser) {
		Map<String, String> map = new HashMap<String, String>();
		StringBuffer sql = new StringBuffer("insert into chf_apply_customs_fee (");
		sql.append("organization_id,apply_id,type,fee_name");
		sql.append(",invoiceid,invoicenum,invoicename,driver_name,invoicestate,currency,exchange_rate");
		sql.append(",price,standard,fobject,fee_modules,remark,create_user,create_time) values ( ");
		sql.append(":organization_id,:apply_id,:type,:fee_name");
		sql.append(",:invoiceid,:invoicenum,:invoicename,:driver_name,:invoicestate,:currency,:exchange_rate");
		sql.append(",:price,:standard,:fobject,:fee_modules,:remark,:create_user,now())");
		map.put("organization_id", loginUser.getOrganizationId() + "");
		map.put("apply_id", applyCustomsFee.getApplyId());
		map.put("type", applyCustomsFee.getType());
		map.put("fee_name", applyCustomsFee.getFeeName());
		map.put("invoicename", applyCustomsFee.getInvoicename());
		map.put("driver_name", applyCustomsFee.getDriverName());
		map.put("invoicestate", applyCustomsFee.getInvoicestate());
		map.put("currency", applyCustomsFee.getCurrency());
		map.put("invoiceid", applyCustomsFee.getInvoiceid());
		map.put("invoicenum", applyCustomsFee.getInvoicenum());
		map.put("exchange_rate", applyCustomsFee.getExchangeRate());
		map.put("price", applyCustomsFee.getPrice());
		map.put("standard", applyCustomsFee.getStandard());
		map.put("remark", applyCustomsFee.getRemark());
		map.put("fobject", applyCustomsFee.getFobject());
		map.put("fee_modules", applyCustomsFee.getFeeModules2());
		map.put("create_user", loginUser.getUserId() + "");
		getMapParameter(sql, map);
		int count = jdbcTemplate.update(sql.toString(), map);
		return count > 0;
	}

	@Override
	public boolean update(ApplyCustomsFee applyCustomsFee, User loginUser) {
		Map<String, String> map = new HashMap<String, String>();
		StringBuffer sql = new StringBuffer("update chf_apply_customs_fee set ");
		sql.append("fee_name=:fee_name,standard=:standard,fee_modules=:fee_modules ");
		sql.append(",invoicestate=:invoicestate,invoicenum=:invoicenum,currency=:currency,exchange_rate=:exchange_rate ");
		sql.append(",price=:price,remark=:remark,update_user=:update_user,update_time=now() where id=:id ");
		map.put("fee_name", applyCustomsFee.getFeeName());
		map.put("standard", applyCustomsFee.getStandard());
		map.put("fee_modules", applyCustomsFee.getFeeModules2());
		map.put("invoicestate", applyCustomsFee.getInvoicestate());
		map.put("invoicenum", applyCustomsFee.getInvoicenum());
		map.put("currency", applyCustomsFee.getCurrency());
		map.put("exchange_rate", applyCustomsFee.getExchangeRate());
		map.put("price", applyCustomsFee.getPrice());
		map.put("remark", applyCustomsFee.getRemark());
		map.put("update_user", loginUser.getUserId() + "");
		map.put("id", applyCustomsFee.getId());
		getMapParameter(sql, map);
		int count = jdbcTemplate.update(sql.toString(), map);
		return count > 0;
	}

	@Override
	public boolean remove(ApplyCustomsFee applyCustomsFee, User loginUser) {
		int count = 0;
		Map<String, String> map = null;
		if(applyCustomsFee.getId().length()>0){
			for(String ID : applyCustomsFee.getId().split(",")){
				map  = new HashMap<String, String>();
				if(ID==null || ID.equals("")){ break; }
				StringBuffer sql =new StringBuffer("update chf_apply_customs_fee set deleted=1" +
						",update_user=:update_user, update_time=now() "
						+ " where id =:id ");
				map.put("update_user", loginUser.getUserId() + "");
				map.put("id", ID);
				getMapParameter(sql, map);
				count = jdbcTemplate.update(sql.toString(),map);
			}
		}
		return count > 0;
	}
}
